package net.gd.servlet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.log4j.Logger;

import net.gd.dao.DBPermissionDAO;
import net.gd.domain.dbPermissionDomain;
import net.gd.utils.Constants;
import net.gd.utils.DAOFactory;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class PermissionServletTest1 {
	private static Logger logger = Logger.getLogger(PermissionServletTest1.class);

	private static PermissionServletTest1 test = new PermissionServletTest1();


	public static void main(String[] args) {
//		test.testInsertTp("2018-01-31 16:20:20","inserTest");
//		test.testUpdateTp("2018-01-31 16:20:20","inserTest");
//		test.testSelectTp("2018-01-31 16:20:20","inserTest","roleId2");
//		test.testSelectTp("2018-01-31 16:20:20","inserTest","admin");
//		test.TestInDbPermission("2018-01-31 16:20:20","inserPermissionTest");
//		test.testUpPermission("2018-01-31 16:20:20","upPMTopic");
//		test.TestUpAuthority("2018-01-31 16:20:20","upAuthority");
//		test.searchTablesPM("2018-01-31 16:20:20","searchTablesPM");
		test.testSelectImportTables("2018-01-31 16:20:20","SelectImportTables");

	}
	
	
	public void testInsertTp(String time,String opType){

		//判断管理员角色
		String userId = "user1";
		String roleId = "roleId1";
		String topicTp = "topicTp1";
		String topicName = "topicName1";

		String message = "";
		String status = "";
		String details = "";
        DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		if (null == userId || "" == userId ||
				"" == roleId ||null == roleId ||
				"" == topicTp || null == topicTp) {
			details = "insert into table topicDetail ,userId or roleId or topicTp is invalide";
			status = "fail";
			message = "userId or roleId or topicTp is invalide,please connect with manager!";
		} else {
			logger.info("insert into table topicDetail");
			details = "insert into topicDetail topicTp: "+topicTp+" ,topicName: "+topicName;
	        String sql = "insert into topicDetail (roleId,topicType,topicName) values (?,?,?)"; 
	        List<Object> params = new ArrayList<Object>(); 
	        if("" == topicName||null == topicName){
	        	topicName = "Default";
	        }
	        params.add(roleId); 
	        params.add(topicTp); 
	        params.add(topicName);
	        
	        try {
				dBPermissionDAO.insert(sql, params);
				status = "sucess";
				message = "success";
			} catch (Exception e) {
				message = "fail";
				status = "fail";
				details = "insert into topicDetail error!"+e;
				System.out.println("insert into topicDetail error!"+e);
				logger.error("insert into topicDetail error!"+e);
			} 
		}
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);

		logger.info(message);
	}
	
	public void testUpdateTp(String time,String opType){
		//判断管理员角色
		String message = "";
		String roleId = "admin";
		String upRoleId = "roleId2";

		//TODO 管理员ID确认
		if("admin" != roleId){
			logger.info("have no authority to update table topicDetail ");
			message = "have no authority to update table topicDetail ";
			return;
		}
		String userId = "userId1";
		String topicID = "2";

		String status = "";
		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		if (null == roleId || "" == roleId 
				|| null == topicID || "" == topicID || 
				"" == userId || null == userId||
				null == upRoleId || "" == upRoleId ) {
			status = "fail";
			details = "update table topicDetail,roleId  or user_id or topicID is invalide!";
			message = "roleId  or user_id or topicID is invalide ,please connect with system manager!";
		} else {
			logger.info("update table topicDetail");
			List<Object> params = new ArrayList<Object>(); 
	        params.add(upRoleId); 
	        params.add(topicID); 
			String sql = "update topicDetail set roleId=? where topicId= ?";
			details = " update topicDetail set roleId= "+upRoleId+" where topicId="+topicID;

			try {
				dBPermissionDAO.insert(sql, params);
				status = "success";	
				message = "success";
			} catch (Exception e) {
				message = "fail";
				status = "fail";
				details = details+e;
				logger.error("update DBPermission fail!"+e);
			}

		}
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
		
		logger.info(message);
	}
	
	public void testSelectTp(String time,String opType,String roleId){
		String sql = "";
		//TODO 管理员ID确认
		if("admin" == roleId){//如果是管理员权限，则是返回所有主题，否则根据roleID进行返回
			sql = "select topicId,topicType,topicName,roleId from topicDetail";
		}else{
			sql="select topicId,topicType,topicName,roleId from topicDetail where roleId = '"+roleId+"'";
		}
		String userId = "userId1";
		JSONArray array = new JSONArray();

		String status = "";
		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		if (null == roleId || "" == roleId || "" == userId || null == userId) {
			status = "fail";
			details = "search topicDetail :roleId or userId is invalide";
		} else {
			logger.info("select table topicDetail");
	        details = sql;
	        try {
				Map<String, Object> dbInfoMap = dBPermissionDAO.search(sql);
				ResultSet rs = null;
				rs = (ResultSet)dbInfoMap.get(Constants.RESULT_SET);
				while(rs.next()){
					//topicId,topicType,topicName,roleId
				    int topicId = rs.getInt(1);
				    String topicType = rs.getString(2);
				    String topicName = rs.getString(3);
					JSONObject jsonObject = new JSONObject();
					jsonObject.put("topicId",topicId);
					jsonObject.put("topicType",topicType); 
					jsonObject.put("topicName",topicName); 
					jsonObject.put("roleId",roleId); 
					array.add(jsonObject);
				    System.out.println("topicId:"+topicId+" topicType："+topicType+" topicName:"+topicName+" , roleId:"+roleId);
					logger.info("select success");
				}
				status = "sucess";
				if (dbInfoMap != null) {
					Connection connection = (Connection) dbInfoMap.get(Constants.CONNECTION);
					if (connection != null)
						connection.commit();
					if (dbInfoMap != null)
						closeCon((Connection) dbInfoMap.get(Constants.CONNECTION),
								(PreparedStatement) dbInfoMap.get(Constants.PREPARED_STATEMENT), rs);
				}
			} catch (Exception e) {
				status = "fail";
				details = "search topicDetail :roleId or userId is invalide"+ e;
				logger.error("select topicDetail error!"+e);
			}
		}
		
		System.out.println(array.toString());
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
	}
	
	private void closeCon(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				logger.error("close resultSet fail", e);
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				logger.error("close preparedStatement fail", e);
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				logger.error("close connection fail", e);
			}
		}
	}
	
	public void TestInDbPermission(String time,String opType){
		//判断管理员角色
		String userId = "userId18";
		String roleId = "roleId2";
		String topicId = "2";
		String dbName = "dbName12";
		String[] tableRdPMs = {"table18_1_1","table19_0_0"};
		List<dbPermissionDomain> tableRdPmList = new ArrayList<dbPermissionDomain>();
		String message = "";
		String status = "";
		String details = "";
        DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		if (null == userId || "" == userId ||
			null == topicId || "" == topicId ||
			null == tableRdPMs || 0 == tableRdPMs.length) {
			details = "insert into table DBPermission,userId or roleId or topicId or table_rdPM_wtPM or topic is invalide";
			status = "fail";
			message = "userId or roleId or topicId or table_rdPM_wtPM or topic is invalide,please connect with manager!";
		} else {
			for(String tableRdPM:tableRdPMs){
				//判断参数个数
				String[] split = tableRdPM.split("_", 3);
				if(3 != split.length){
					details = "insert into table DBPermission,table_rdPM_wtPM lentgh is invalide, length :"+split.length;
					status = "fail";
					message = "table_rdPM_wtPM lentgh is invalide, length :"+split.length+",please connect with manager!";
					PermissionServlet ser = new PermissionServlet();
					ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
					logger.info(message);
					return;
				}else{
					dbPermissionDomain tableRdPm = new dbPermissionDomain();
					tableRdPm.setId(Integer.parseInt(topicId));
					tableRdPm.setDbname(dbName);
					tableRdPm.setTopicid(topicId);
					tableRdPm.setTablename(split[0]);
					tableRdPm.setRdpm(Integer.parseInt(split[1]));
					tableRdPm.setWtpm(Integer.parseInt(split[2]));
					tableRdPmList.add(tableRdPm);
				}
			}
			logger.info("insert into table DBPermission");
			details = "insert into DBPermission topicId:"+topicId+" ,dbName: "+dbName+
					" ,tableRdPMs: "+tableRdPMs.toString();
			String sql = "insert into DBPermission (topicId,dbName,tableName,rdPM,wtPM) values (?,?,?,?,?)"; 
	        try {
				dBPermissionDAO.inserPermission(sql, tableRdPmList);
				status = "sucess";
				message = "success";
			} catch (Exception e) {
				message = "fail";
				status = "fail";
				details = "insert into DBPermission error!"+e;
				logger.error("insert into DBPermission error!"+e);
			} 
		}
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
		logger.info(message);
	}
	
	public void testUpPermission(String time,String opType){
		//判断管理员角色
		String roleId = "roleId";
		String userId = "userId12";
		String topicId = "111111111111";
		String curTopic = "2";
		String[] dbTables = {"dbName12_table18","dbName12_table19"};

		String message = "";
		String status = "";
		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		if (null == roleId || "" == roleId || null == topicId || "" == topicId 
			|| "" == userId || null == userId ||  null == dbTables || 0 == dbTables.length) {
			status = "fail";
			details = "roleId  or user_id or topic or tableName is invalide!";
			message = "roleId  or user_id or topic or tableName is invalide ,please connect with system manager!";
		} else {
			logger.info("update table DBPermission");
			HashMap<String,dbPermissionDomain>  tabletopicMap = new HashMap<String, dbPermissionDomain>();

			for(String dbTable:dbTables){
				dbPermissionDomain permiss = new dbPermissionDomain();
//				permiss.setId(UUID.randomUUID());
				permiss.setTopicid(curTopic);
				tabletopicMap.put(dbTable, permiss);
			}
			//判断是删除还是修改主题ID
			String sql = "";
			if(opType.equals("upPMTopic")){
				sql = "update DBPermission set topicId='"+topicId+"' where dbName=? and tableName=? and topicId =?";
			}else if(opType.equals("deletePMTopic")){
				sql = "update DBPermission set topicId='DEFAULT' where dbName=? and tableName=? and topicId =?";
			}
			try {
				dBPermissionDAO.update(sql, tabletopicMap);
				status = "success";	
				message = "success";
				details = "update DBPermission set topic='"+topicId+"', roleId:"+roleId+" ,topicId: "+topicId+" ,tableNames:"+dbTables.toString();
			} catch (Exception e) {
				message = "fail";
				status = "fail";
				details = "update DBPermission error!"+e;
				logger.error("update DBPermission fail!"+e);
			}

		}
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
		logger.info(message);

	}
	public void TestUpAuthority(String time,String opType){
		//判断管理员角色
		String roleId = "role2";
		String message = "";
		
		String userId = "userId1";
		String topicId = "111111111111";
		String dbName = "dbName12";
		String tableRdPM = "table18_9_8";

		String status = "";
		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();
		String sql = "";
		if (null == roleId || "" == roleId || null == topicId || "" == topicId 
			|| "" == userId || null == userId || null == dbName || "" == dbName 
			|| "" == tableRdPM || null == tableRdPM) {
			status = "fail";
			details = "roleId  or user_id or topic or tableName is invalide!";
			message = "roleId  or user_id or topic or tableName is invalide ,please connect with system manager!";
		} else {
			//判断参数个数
			String[] split = tableRdPM.split("_", 3);
			if(3 != split.length){
				details = "update table DBPermission PM,table_rdPM_wtPM lentgh is invalide, length :"+split.length;
				status = "fail";
				message = "table_rdPM_wtPM lentgh is invalide, length :"+split.length+",please connect with manager!";
				logger.info(message);
				return;
			}else{
				logger.info("update table DBPermission PM");
				sql = "update DBPermission set rdPM = ? ,wtPM = ? where topicId = ? and tableName=? and dbName=?";
				List<Object> params = new ArrayList<Object>(); 
				String rdPM = split[1];
				String wtPM = split[2];
				String tableName = split[0];
		        params.add(rdPM); 
		        params.add(wtPM); 
		        params.add(topicId); 
		        params.add(tableName); 
		        params.add(dbName); 
		        details = "update DBPermission set rdPM = "+rdPM+" ,wtPM = "+wtPM+" where topicId = "+topicId+" and tableName= "+tableName+" and dbName="+dbName;

				try {
					dBPermissionDAO.insert(sql, params);
					status = "success";	
					message = "success";
				} catch (Exception e) {
					message = "fail";
					status = "fail";
					details = details+e;
					logger.error("update DBPermission fail!"+e);
				}
			}
		}
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
		logger.info(message);
	}
	
	public void searchTablesPM(String time,String opType){
		//判断管理员角色
		String roleId = "roleId1";
		String userId = "userId";
		String topicId = "3";
		JSONArray array = new JSONArray();

		String status = "";
		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		if (null == roleId || "" == roleId || "" == userId || null == userId
				|| "" == topicId || null == topicId) {
			status = "fail";
			details = "search tables:roleId or topic  or user_id is invalide";
			logger.info(details);
		} else {
			logger.info("select table DBPermission");
	        String sql = "select t2.topicName,t1.dbName,t1.tableName,t1.rdPM,t1.wtPM from DBPermission t1 join topicDetail t2 on t1.topicId = t2.topicId where t1.topicId="+topicId; 
	        details = sql;
	        logger.info(sql);
	        try {
				Map<String, Object> dbInfoMap = dBPermissionDAO.search(sql);
				ResultSet rs = null;
				rs = (ResultSet)dbInfoMap.get(Constants.RESULT_SET);
				while(rs.next()){
				    String topicName = rs.getString(1);
				    String dbName = rs.getString(2);
				    String tableName = rs.getString(3);
				    int rdPM = rs.getInt(4);
				    int wtPM = rs.getInt(5);
					JSONObject jsonObject = new JSONObject();
					jsonObject.put("topicName",topicName);
					jsonObject.put("dbName",dbName); 
					jsonObject.put("tableName",tableName); 
					jsonObject.put("rdPM",rdPM); 
					jsonObject.put("wtPM",wtPM); 

					array.add(jsonObject);
				    System.out.println("tableName:"+tableName+" rdPM："+rdPM+" wtPM："+wtPM);
					logger.info("select success");
				}
				status = "sucess";
				if (dbInfoMap != null) {
					Connection connection = (Connection) dbInfoMap.get(Constants.CONNECTION);
					if (connection != null)
						connection.commit();
					if (dbInfoMap != null)
						closeCon((Connection) dbInfoMap.get(Constants.CONNECTION),
								(PreparedStatement) dbInfoMap.get(Constants.PREPARED_STATEMENT), rs);
				}
			} catch (Exception e) {
				status = "fail";
				logger.error("select error!"+e);
			}
			
		}
		System.out.println(array.toString()); 
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
		}
	
	public void testSelectImportTables(String time,String opType){
		//判断管理员角色
		String roleId = "roleId1";
		String userId = "userId1";
		String topicId = "111111111111";
		JSONArray array = new JSONArray();
		String status = "";
		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();

		logger.info("select table DBMANAGE");
        String sql = "select * from "
			    		+ "(select DATABASE_NAME,"
					    		+ "SOURCE_TABLE,"
					    		+ "concat_ws('_',DATABASE_NAME,SOURCE_TABLE) as dbTable "
			    		+ "from DBMANAGE) t1 "
			    		+ "where dbTable not in "
					    		+ "(select concat_ws('_',dbName,tableName) "
					    		+ "from DBPermission "
					    		+ "where topicId='"+topicId+"')"; 
        details = sql;
        try {
			Map<String, Object> dbInfoMap = dBPermissionDAO.search(sql);
			ResultSet rs = null;
			rs = (ResultSet)dbInfoMap.get(Constants.RESULT_SET);
			while(rs.next()){
			    String dbName = rs.getString(1);
			    String tableName = rs.getString(2);
				JSONObject jsonObject = new JSONObject();
				jsonObject.put("dbName",dbName); 
				jsonObject.put("tableName",tableName); 

				array.add(jsonObject);
			    System.out.println("dbName: "+dbName+",tableName:"+tableName);
				logger.info("select success");
			}
			status = "sucess";
			if (dbInfoMap != null) {
				Connection connection = (Connection) dbInfoMap.get(Constants.CONNECTION);
				if (connection != null)
					connection.commit();
				if (dbInfoMap != null)
					closeCon((Connection) dbInfoMap.get(Constants.CONNECTION),
							(PreparedStatement) dbInfoMap.get(Constants.PREPARED_STATEMENT), rs);
			}
		} catch (Exception e) {
			status = "fail";
			logger.error("select error!"+e);
		}
        System.out.println(array.toString());
		PermissionServlet ser = new PermissionServlet();
		ser.insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
	}

	
}
